package com.hshh.util;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.net.URL;
import java.net.URLEncoder;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.hshh.common.Constant;

public class ExcelUtil {
	private Sheet sheet;
	private Workbook workbook;
	
	private int startRow = 0;
	private int startCell = 0;
	
	private List<Map<String, Object>> data = null;
	private String[] header = null;
	
	
	public ExcelUtil(String loadFileName){
//		FileInputStream file;
		try {
			String filePath = Constant.HTTP_PROTOCOL + Constant.DEFAULT_URL + "/excel/" + loadFileName + ".xls";
			System.out.println("ExcelLocation : " + filePath);
			URL url = new URL(filePath);
			workbook = new HSSFWorkbook(url.openStream());
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	
	//작성할 Row 시작 위치를 지정
	public void setStartRow(int startRow){
		this.startRow = startRow;
	}
	//작성할 Cell 시작 위치 지정
	public void setStartCell(int startCell){
		this.startCell = startCell;
	}
	public List<Map<String, Object>> getData(){
		return data;
	}
	public void setData(List<Map<String, Object>> data){
		this.data = data;
	}
	public String[] getHeader(){
		return header;
	}
	public void setHeader(String[] header){
		this.header = header;
	}
	
	//Sheet의 Row정보
	public Row getRow(int row){
		Row r = sheet.getRow(row);
		if(r == null){
			r = sheet.createRow(row);
		}
		return r;
	}
	
	public Row getNewRow(int row){
		Row r = sheet.createRow(row);
		return r;
	}
	//Row의 Cell정보
	public Cell getCell(int row, int cell){
		Row r = getRow(row);
		Cell c = r.getCell(cell);
		
		if(c == null){
			c = r.createCell(cell);
		}
		return c;
	}
	
	public Cell getNewCell(int row, int cell){
		Row r = getRow(row);
		Cell c = r.createCell(cell);
		return c;
	}
	
	//Cell에 데이터 기록
	public void setCellValue(int row, int cell, boolean cellValue){
		Cell c = getCell(row, cell);
		c.setCellValue(cellValue);
		c.setCellStyle(getCell(startRow, cell).getCellStyle());
	}
	public void setCellValue(int row, int cell, Calendar cellValue){
		Cell c = getCell(row, cell);
		c.setCellValue(cellValue);
		c.setCellStyle(getCell(startRow, cell).getCellStyle());
	}
	public void setCellValue(int row, int cell, Date cellValue){
		Cell c = getCell(row, cell);
		c.setCellValue(cellValue);
		c.setCellStyle(getCell(startRow, cell).getCellStyle());
	}
	public void setCellValue(int row, int cell, double cellValue){
		Cell c = getCell(row, cell);
		c.setCellValue(cellValue);
		c.setCellStyle(getCell(startRow, cell).getCellStyle());
	}
	public void setCellValue(int row, int cell, RichTextString cellValue){
		Cell c = getCell(row, cell);
		c.setCellValue(cellValue);
		c.setCellStyle(getCell(startRow, cell).getCellStyle());
	}
	public void setCellValue(int row, int cell, String cellValue){
		Cell c = getCell(row, cell);
		c.setCellValue(cellValue);
		c.setCellStyle(getCell(startRow, cell).getCellStyle());
	}
	
	public void writeExcel(HttpServletResponse response, String filename){
		long start = System.currentTimeMillis();
		sheet = workbook.getSheetAt(0);

		String[] headers = getHeader();
		List<Map<String, Object>> list = getData();
		//데이터 설정
		for(int i = 0 ; i < list.size() ; i++){
			for(int j = 0 ; j < headers.length ; j++){
				Object obj = list.get(i).get(headers[j]);
				if("NM_DEPT".equals(headers[j])){
					if(list.get(i).get("LB_DEPT") != null && !"".equals(list.get(i).get("LB_DEPT"))){
						int lbDetp = Integer.parseInt(list.get(i).get("LB_DEPT").toString());
						String frontStr = "";
						for(int k = 0 ; k < lbDetp ; k++){
							frontStr += "____";
						}
						obj = frontStr + obj;
					}
				}
								
				if(isNum(obj)){
					setCellValue(i + startRow, j + startCell, Double.parseDouble(obj.toString()));
				}
				else if(obj instanceof String){
					setCellValue(i + startRow, j + startCell, obj.toString());
				}
				else if(obj instanceof Boolean){
					setCellValue(i + startRow, j + startCell, Boolean.parseBoolean(obj.toString()));
				}
				else if(obj == null){
					setCellValue(i + startRow, j + startCell, "");
				}
			}
		}
		try {
			filename = URLEncoder.encode(filename, "UTF-8");
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + ".xls\";");
			workbook.write(response.getOutputStream());
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		long end = System.currentTimeMillis();
		System.out.println("HSSF WRITE : " + (end - start));
	}
	
	private boolean isNum(Object obj){
		try{
			Double.parseDouble(obj.toString());
		}
		catch (Exception e) {
			// TODO: handle exception
			return false;
		}
		return true;
	}
}
